Questions: Business Intelligence

This section covers some frequently asked questions about BI in the following areas:

BI Reports

Can I run the same report in different companies?

You cannot do this if the companies have a different nominal ledger structure or different sales analysis codes. If you have selected a field, such as Customer, for one company and you then select a different company that does not have that field, the report will reset itself.

How do I send reports to non Sage 200 BI users?

You should:

  1. Change the report type to static.
  2. Save the report then send it to the user.

How do I change my report type?

There are two types of report: Dynamic and Static. All Sage 200 BI reports are dynamic - they consist of a range of formulas and styles. A static report has no formulas, styles or formatting. However, you can apply Microsoft Excel styles and formats to a static report.

To change the report type to static
  • Right-click any cell in the report and choose Report Type > Static.
To change the report type to dynamic
  • Right-click the report title cell (the top left cell of the report) and choose Report Type > Dynamic.

How do I save or change a report?

The Sage 200 BI standard reports are read-only. You can make changes to the reports and save them using the Microsoft Excel Save As command.

Note: There is no guarantee that any customised reports will work with future versions of the software. This is because the structure of the cubes may change.

You must save an edited report with a unique name.

How do I add analysis codes to reports?

Report criteria appear at the top of the report underneath the report title and company name.

By default, analysis codes do not appear on standard reports as report criteria. You can, however, add them using the Field List (to select from) pane on the Report Layout panel.

  • To place the analysis codes on the report as report criteria, select the check box next to relevant code.

  • To place the analysis codes on the report rows or columns, click and drag the code from the Field List (to select from) pane to the Report Rows or Report Columns panes.
  • To add the codes to the report, click Apply.

Note: For more information about the Report Layout panel, please refer to the Sage 200 BI help topic Report Layout.

How do I change from fixed to variable rows and columns?

A report can have variable or fixed rows and columns. When a report has variable rows and columns, the report is redrawn each time it is changed. When a report with fixed rows and columns is changed, the report is recalculated but not redrawn. Some of the standard reports have fixed rows and columns. You can change these to variable rows and columns as follows:

  1. Open the Report Layout Options window.
  2. On the Report Layout Options window, select the General tab.

  3. Select Variable Rows & Columns.

    Tip: To expand or hide report row or column data, double-click on the headings and select Allow drill down on Report.

  4. Click OK.

How do I change row and column referencing?

Usually, in a Microsoft Excel worksheet, rows are numbered from 1 upwards and columns are lettered from A upwards.

Some worksheets use the 'R1C1' method of cell referencing where both rows are numbered from 1 upwards.

If you prefer to use the more common A1 style of referencing cells, it can be helpful to change the referencing style. You can do this as follows:

  1. Select the File tab then click Options. The Excel Options window appears.
  2. On the left-hand pane, click Formulas.
  3. Under Working with formulas, clear the R1C1 reference style check box and click OK.

BI Reports

Company name and time not updated in a modified standard report

A modified report is a standard report that has been changed and saved.

These modified Sage 200 BI reports may display the incorrect company name and time if:

  • You open a standard BI report from the Sage 200 menu bar and save a modified copy.
  • Next time, you open the modified report in Excel.

The company name and time information on the modified report will not be refreshed in the version displayed in Excel. The report will still show the company, date and time information from the previous time that the report was created.

Note: The data in the report will be correct.

To avoid this problem, we recommend that you run your modified reports from the Sage 200 menu bar.

Profit & Loss and Balance Sheets layouts with the same name

If you have Profit & Loss and Balance Sheet layouts with the same name:

  • You cannot refresh your data cubes.
  • These reports will show the same categories (i.e. they will show the same information).

To display the correct information, the default financial statement layouts in Sage 200 must have different names.

You set up this name in the Report Settings on the Nominal.

The filters do not display on the report

This can occur if you have entered conflicting criteria for your filters using attributes from the same dimension. For example, you have a filter for both country code and country name on the same report. You select a country code and then select a different country name.

To resolve this, open the report layout to change the filters.

Customised reports showing out of date information

Whilst loading a new or customised report, BI will display the data that was current when the report was created or customised. This may not reflect the current accounts data.

Once the report has been fully loaded, this situation will be corrected and BI will display the most up-to-date information.

Report layout screen appears by default

The Report Layout Screen may appear when a report is loaded. Close this screen if it is not needed. This does not affect the data in the report.

Adding filters to reports

When attempting to add a filter via the Report Layout Wizard, the Add-in may restore the previous query and continue. When this occurs a message box appears.

Unexpected results from the Inactive Supplier and Inactive Customer Reports

The Inactive Supplier and Inactive Customer reports measure activity based on transaction period, rather than transaction date.

If a customer or supplier has a transaction in a recent period, they will not be flagged as inactive.

Errors when creating a new connection

When creating a new report connection, the following may occur:

  • The Server Cubes drop-down list is empty.

    If this occurs, enter the name of the Analysis Services Instance and then click Next.

  • Using the Back and Next options produces an error message.

    If this occurs, cancel the connection and start again.

Sub totals not appearing when using Suppress Empty/Rows columns

In some circumstances, Sub-Totals are not displayed on BI reports when the Suppress Empty Rows/columns option is selected.

We recommend that you do not suppress empty rows and columns if you want to display sub-totals on your reports.

Cannot edit an Excel spreadsheet embedded in a word document

To edit an Excel spreadsheet that is embedded in a Word document, you must disable the BI Excel Add-in first.

If the BI Excel Add-in is not disabled the following error message is displayed:

The program used to create this object is Excel. That program is not installed on your computer...

  1. Close Microsoft Excel and Microsoft Word.
  2. Use the Windows Task Manager to close any Microsoft Excel processes that may still be running.
  3. Disable the BI Excel Add-In.

The layout pane does not automatically select items that are not on the first page

If you have selected an item with more than 100 members, the list of members is displayed on the Layout pane, on pages of 100 items each.

You can add an item to a report with members selected from any page of the list. If you then amend the selections for that item, any items originally selected that are not on the first page (i.e. not in the first 100), will not remain selected. Make sure you reselect these items, before you apply your changes.

Excel Add-in

How do I remove the drill indicator?

In Sage 200 BI, cells that allow you to expand or hide information have the following indicator:

If required, you can remove this:

  1. On the Microsoft Excel ribbon, select the Home tab and in the Styles group, click Cell Styles.
  2. Right-click IAParentRowHeader or IAParentColumnHeader and choose Modify. The Style window appears.
  3. Clear the Number check box and click OK.
  4. To apply the change, click Report Layout on the SageBI tab.
  5. On the Report Layout panel, click Apply.

Why are some options not available on the Sage BI ribbon

Some options on the BI ribbon are only available when a cell containing data is selected on the current BI report.

BI Administration tool

When I update my cubes, can I see a log of events?

You can use Windows Event Viewer to see when you have performed a cube update.

The Event Viewer displays this information in the 200 BI Diagnostics area; or, if using Windows Vista, in Application and Service Logs \ Sage 200 BI Diagnostics.

How do I delete the Data Warehouse and Analysis Services databases?

  1. Open the Sage 200 BI Admin Utility.
  2. On the Data Warehouse Configurationwindow, highlight the appropriate Sage 200 company. Press the Delete key.
  3. You will be prompted to confirm deleting the Data Warehouse and Analysis Services databases for the company. Click Yes to delete the databases.
  4. You will then be prompted to confirm whether you want to delete the report settings for the company. If you intend to recreate the databases, you may want to click No and keep the report settings.

Sage 200 desktop

How do I add a BI report to the menu in Sage 200?

Before you add a report to the Sage Business Intelligence menu, you should ensure that the report resides in the location where the reports are installed. By default, this is C:\Sage\Reports\Business Intelligence followed by the relevant folder, e.g. User Defined.

Add a new feature in System Administration

  1. Choose Start > Programs > Sage Tools > Sage 200 > System Administration. The Logon window appears.
  2. Enter your logon details and click OK. The System Administration window appears.
  3. From the Navigation bar, click Features.
  4. Open the BI Reports folder.

  5. Right-click the User Defined folder, and select Add New Feature.

  6. Enter the Name. Click New GUID to enter a GUID for the feature.
  7. Click OK to add the new feature.
  8. To ensure the new feature has appropriate authorisation, right-click the feature and select Role Authorisation. Add the required roles as authorised roles and click OK.

  9. Select the new feature, and in the Feature Properties pane select the Targets tab and click Add. The Add New Target window appears.

  10. Enter a Target Name.
  11. Set the Target Type as Form.

  12. For the Target Action, type the path to the report file in the reports folder, without including the .xls file extension.

    For example, say your report is located in Sage\Reports\Business Intelligence\User Defined\My Report.xls. For the Target Action, you would just enter User Defined\My Report.

    Note: You cannot use the browse (...) button to select the path to the report.

  13. Click New GUID to enter a Target GUID.
  14. Enter a Target Description.
  15. Set the Target Launcher as Sage.MMS.BILauncher.
  16. To save the target properties, click Save.
  17. You can now close System Administration. Next add a new menu item for this feature in Sage 200
Add a new menu item in Sage 200
  1. Open Sage 200
  2. Click at the top of the menu, to change it to Edit mode.

  3. Click and select New Menu Item.

  4. Enter the menu item Title.

  5. Select the target you created In System Administration from the target tree.

    This will be located in BI Reports > User Defined.

  6. Click OK.
  7. The new menu item appears the bottom of your menu. You can then drag it to the required folder.
See also

Set up Business Intelligence